package com.lyq.database;

import com.lyq.bean.mySql.MysqlLinking;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class CheckCollation {
    public static void main(String[] args) {
        // 创建数据库连接
        MysqlLinking mysqlLinking = new MysqlLinking();
        mysqlLinking.getLink();
        Connection conn = MysqlLinking.conn;
        
        if (conn != null) {
            System.out.println("数据库连接成功！");
            
            try {
                Statement stmt = conn.createStatement();
                
                // 检查数据库的字符集和排序规则
                ResultSet rs = stmt.executeQuery("SHOW VARIABLES LIKE 'collation%';");
                System.out.println("数据库排序规则设置：");
                while (rs.next()) {
                    System.out.println(rs.getString("Variable_name") + " = " + rs.getString("Value"));
                }
                rs.close();
                
                // 检查electcourse表的字符集和排序规则
                rs = stmt.executeQuery("SHOW FULL COLUMNS FROM electcourse;");
                System.out.println("\nelectcourse表列信息：");
                System.out.println("字段名\t\t类型\t\t排序规则");
                System.out.println("----------------------------------------");
                while (rs.next()) {
                    System.out.println(rs.getString("Field") + "\t\t" + 
                                     rs.getString("Type") + "\t\t" + 
                                     rs.getString("Collation"));
                }
                rs.close();
                
                // 检查course表的字符集和排序规则
                rs = stmt.executeQuery("SHOW FULL COLUMNS FROM course;");
                System.out.println("\ncourse表列信息：");
                System.out.println("字段名\t\t类型\t\t排序规则");
                System.out.println("----------------------------------------");
                while (rs.next()) {
                    System.out.println(rs.getString("Field") + "\t\t" + 
                                     rs.getString("Type") + "\t\t" + 
                                     rs.getString("Collation"));
                }
                rs.close();
                
                // 应用修复脚本
                System.out.println("\n应用排序规则修复脚本...");
                stmt.execute("ALTER TABLE electcourse CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;");
                System.out.println("已修改electcourse表的排序规则");
                
                // 验证修改结果
                rs = stmt.executeQuery("SHOW FULL COLUMNS FROM electcourse;");
                System.out.println("\n修改后electcourse表列信息：");
                System.out.println("字段名\t\t类型\t\t排序规则");
                System.out.println("----------------------------------------");
                while (rs.next()) {
                    System.out.println(rs.getString("Field") + "\t\t" + 
                                     rs.getString("Type") + "\t\t" + 
                                     rs.getString("Collation"));
                }
                rs.close();
                
                // 关闭数据库连接
                conn.close();
                System.out.println("\n数据库连接已关闭。");
            } catch (SQLException e) {
                System.err.println("查询数据库信息时发生错误: " + e.getMessage());
                e.printStackTrace();
            }
        } else {
            System.err.println("数据库连接失败！");
        }
    }
}